﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class DanTocDAL
    {
        public static List<DanTocPublic> LoadDanToc()
        {
            var dantoc = Common.db.ExecuteStoreQuery<DanTocPublic>(@"select * from DanToc");
            //int a= dantoc.ToList().Count;
            return dantoc.ToList();

            //List<DanTocPublic> list = new List<DanTocPublic>();
            //var query = from q in Common.db.DanTocs
            //            select q;
            //foreach (var item in query)
            //{
            //    DanTocPublic tg = new DanTocPublic(item.MaDT, item.TenDanToc);
            //    list.Add(tg);
            //}
            //return list;
        }
        public static DanTocPublic GetDanTocByMaDT(int MaDT)
        {
            var dantoc = Common.db.ExecuteStoreQuery<DanTocPublic>(@"select TenDanToc from DanToc where MaDT=" + MaDT);
            return dantoc.FirstOrDefault();
        }
        //public static DanTocPublic LoadDanTocByMaNK(int MaNK)
        //{
        //    var dt = Common.db.ExecuteStoreQuery<DanTocPublic>(@"select TenDanToc from DanToc where MaNK=" + MaNK);
        //    return dt.FirstOrDefault();
        //}
        public static List<DanTocPublic> LoadBieuDoDanToc()
        {
            string chuoi = @"
                            select first.MaDT, first.TenDanToc, first.SL
                            from (
	                            select top 5 D.MaDT,TenDanToc,COUNT(N.MaDT) as SL
	                            from DanToc D join Nhankhau N
	                            on D.MaDT = N.MaDT
	                            group by D.MaDT, TenDanToc
	                            order by SL desc) first
	
                            union all
	
                            select last.MaDT, last.TenDanToc, last.SL
                            from (
	                            select cast(0 as tinyint) as MaDT, 'Khác' as TenDanToc,ISNULL(sum(t2.SL),0) as SL
	                            FROM
	                            (   SELECT ROW_NUMBER() OVER(ORDER BY SL desc) AS row, t1.*
		                            FROM
		                            ( 	select COUNT(N.MaDT) as SL
			                            from DanToc D left join Nhankhau N
			                            on D.MaDT = N.MaDT
			                            group by D.MaDT
		                            ) t1
	                            ) t2
	                            WHERE t2.row BETWEEN 6 AND 100) last; 
                            ";
            var dt = Common.db.ExecuteStoreQuery<DanTocPublic>(chuoi);
            return dt.ToList();
        }
    }
}
